package com.jian.java;

import org.junit.Test;

import java.io.*;
import java.sql.*;
import java.util.Properties;

/**
 * @author Jiange
 * @create 2021-04-04-10:56
 * 向数据库插入blob数据
 */
public class BlobTest {
    @Test
    public void test1() throws Exception{
        char[] chars = new char[5];
        InputStreamReader inputStreamReader = new InputStreamReader(new FileInputStream("JDBC.properties"));
        int read = inputStreamReader.read(chars);
        Properties properties = new Properties();
        properties.load(inputStreamReader);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        Class.forName(driverClass);

        Connection connection = DriverManager.getConnection(url, user, password);

//        String insert = "update `students` set photo=? where sno=5";

        String select="select photo jian from `students` where sno=5";
        PreparedStatement preparedStatement = connection.prepareStatement(select);
        ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
        int parameterCount = parameterMetaData.getParameterCount();//获取占位符的个数

//        FileInputStream fileInputStream = new FileInputStream("D:\\自己编程前端练习文件\\tupian\\2.jpg");
//
//        preparedStatement.setBlob(1,fileInputStream);
//
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();



        while (resultSet.next()){
            Blob jian = resultSet.getBlob("jian");
            System.out.println(jian);
            byte[] bytes = new byte[1024];
            int lai;
            InputStream binaryStream = jian.getBinaryStream();
            FileOutputStream fileOutputStream = new FileOutputStream("jian.png");
            while ((lai=binaryStream.read(bytes)) != -1){
                fileOutputStream.write(bytes,0,lai);
            }
            inputStreamReader.close();
            fileOutputStream.close();
        }

        connection.close();
        inputStreamReader.close();
//
//        ResultSetMetaData metaData = resultSet.getMetaData();
//
//        preparedStatement.setInt(1,10);
//        preparedStatement.setBlob(2,);
//
//        while(resultSet.next()){
//            resultSet.getString(1);
//        }
//        FileInputStream fileInputStream = new FileInputStream("JDBC.properties");
//        byte[] bytes = fileInputStream.readAllBytes();
//        System.out.println(new String(bytes));
//
//        Blob photo = resultSet.getBlob("photo");
//
//        InputStream binaryStream = photo.getBinaryStream();
//
//        connection.close();

    }
    @Test
    public void test2() throws Exception {
        //批量插入
        InputStream inputStream = new FileInputStream("JDBC.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        Class.forName(driverClass);

        Connection connection = DriverManager.getConnection(url, user, password);
        connection.setAutoCommit(false);

        String insert="insert into book(id,bname,pubdate) values (?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(insert);
        for (var i=0;i<200000;i++){
            preparedStatement.setObject(1,i);
            preparedStatement.setObject(2,"Name_"+i);
            preparedStatement.setObject(3,new Timestamp(System.currentTimeMillis()));
//            preparedStatement.execute();
            preparedStatement.addBatch();//1.攒
            if(i%1000==0){
                preparedStatement.executeBatch();//执行batch
                preparedStatement.clearBatch();//清空batch
            }
            if (i==(200000-1)){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        connection.commit();
        connection.close();
        inputStream.close();
    }
}
